package com.fc.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.fc.db.DBConnect;
import com.fc.model.Coach;
import com.fc.model.Member;
import com.fc.util.Pagination;

public class MemberControl {
	
	public static Pagination pageInstance = null;

	//add member
	public static boolean addMember(Member member){
		DBConnect dbc = new DBConnect();
		String sql = "insert into member (store_id,sex,kind,name,end_time,username,password,phone,start_time,idcard) " +
				"value(?,?,?,?,?,?,?,?,?,?)";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1,member.getStore_id());
			dbc.setInt(2,member.getSex());
			dbc.setInt(3,member.getKind());
			dbc.setString(4,member.getName());
			dbc.setString(5,member.getEnd_time());
			dbc.setString(6,member.getUsername());
			dbc.setString(7,member.getPassword());
			dbc.setString(8,member.getPhone());
			dbc.setString(9,member.getStart_time());
			dbc.setString(10,member.getIdcard());
			dbc.executeUpdate();
			//System.out.println("222");
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//delete member
	public static boolean deleteMember(int id){
		DBConnect dbc = new DBConnect();
		String sql = "delete from member where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setInt(1, id);
			dbc.executeUpdate();
			System.out.println("222");
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//update member
	public static boolean updateMember(Member member,int id){
		DBConnect dbc = new DBConnect();
		String sql = "update member set start_time=?,kind=?,end_time=?,state=?,password=?,phone=? where id=?";
		dbc.prepareStatement(sql);
		try{
			dbc.setString(1,member.getStart_time());
			dbc.setInt(2, member.getKind());
			dbc.setString(3, member.getEnd_time());
			dbc.setInt(4, member.getState());
			dbc.setString(5, member.getPassword());
			dbc.setString(6, member.getPhone());
			dbc.setInt(7, id);
			dbc.executeUpdate();
			return true;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			dbc.close();
		}
	}
	//get member information by id
	public static Member getMemberInfo(int id){
		DBConnect dbc = new DBConnect();
		String sql = "select * from member where id=?";
		dbc.prepareStatement(sql);
		try{
			Member member = new Member();
			dbc.setInt(1, id);
			ResultSet rs = dbc.executeQuery();	
		if(rs.next()){
			member.setStore_id(rs.getInt("store_id"));
			member.setStart_time(rs.getString("start_time"));
			member.setKind(rs.getInt("kind"));
			member.setState(rs.getInt("state"));
			member.setSex(rs.getInt("sex"));
			member.setDegree(rs.getInt("degree"));
			member.setEnd_time(rs.getString("end_time"));
			member.setUsername(rs.getString("username"));
			member.setPassword(rs.getString("password"));
			member.setPhone(rs.getString("phone"));
			member.setIdcard(rs.getString("idcard"));
			member.setName(rs.getString("name"));
			return member;
		}
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			dbc.close();
		}
		return null;
	}
	//colletion members
	public static Collection<Member>getAllMembers(){
		DBConnect dbc = new DBConnect();
		String sql = "select * from member";
		Collection<Member> memberlist = new ArrayList<Member>();
		ResultSet rs = null;
		Member member = null;
		dbc.prepareStatement(sql);
		try{
			rs = dbc.executeQuery();
			while(rs.next()){
				member = new Member();
				member.setStore_id(rs.getInt("store_id"));
				member.setStart_time(rs.getString("start_time"));
				member.setKind(rs.getInt("kind"));
				member.setState(rs.getInt("state"));
				member.setSex(rs.getInt("sex"));
				member.setDegree(rs.getInt("degree"));
				member.setEnd_time(rs.getString("end_time"));
				member.setUsername(rs.getString("username"));
				member.setPassword(rs.getString("password"));
				member.setPhone(rs.getString("phone"));
				member.setIdcard(rs.getString("idcard"));
				member.setName(rs.getString("name"));
				memberlist.add(member);
			}
			return memberlist;
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}finally{
				dbc.close();
			}
		}				
	public Collection getMember(int page,int rowsPerPage)throws SQLException,Exception{
		String sql = "select * from member";
		pageInstance = new MemberControlPage(sql,rowsPerPage);
		try{
			return pageInstance.getPage(page);
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
}

class MemberControlPage extends Pagination{
	public MemberControlPage(String sql,int rowsPerPage){
		super(sql,rowsPerPage,1);
	}
	@Override
	protected Collection<Member> packResultSet(ResultSet rs) throws SQLException {
		Collection<Member> memberList = new ArrayList<Member>();
		while (rs.next()) {
			Member member=new Member();
			member.setId(rs.getInt("id"));
			member.setStore_id(rs.getInt("store_id"));
			member.setName(rs.getString("name"));
			member.setKind(rs.getInt("kind"));
			//member.setPhone(rs.getString("phone"));
			member.setStart_time(rs.getString("start_time"));
			member.setSex(rs.getInt("sex"));
			member.setState(rs.getInt("state"));
			//coach.setCoach_time(rs.getInt("coach_time"));
			memberList.add(member);
			member = null;
		}
		return memberList;
	}
	
}
